﻿Imports System.Data
Imports System.Data.SqlClient
Public Class Frm_Demande_Clients_loc
    Private Sub vider()
        cmbCategorie.SelectedIndex = -1
        cmbType.SelectedIndex = -1
        txtQuantite.Clear()
        txt_prix.Clear()
        Cmd.CommandText = "select ISNULL(CONVERT(int,MAX(numLocation)),0) + 1 from trLocation"
        txtNumBon.Text = Cmd.ExecuteScalar
        If cmbClient.Items.Count > 0 Then
            cmbClient.SelectedIndex = 0
        End If
        If cmbPerson.Items.Count > 0 Then
            cmbPerson.SelectedIndex = 0
        End If
        dtpLoc.Value = Now
        dtpLivraison.Value = Now
        Me.dtpRecup.MinDate = New Date(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day, dtpLivraison.Value.Hour, 0, 0)
        Me.dtpRecup.MinDate.AddHours(5)
        txtAdresse.Clear()
        lstVEquipLoue.Items.Clear()
    End Sub
    Private Sub Frm_Demande_Clients_loc_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Me.MdiParent = Frm_Main
            'remplir Cobo Clients
            Dim htClient As New Hashtable()
            Dim str As String = ""
            cmbClient.Items.Clear()
            Cmd = New SqlCommand("Select * From Clients where Location='Oui'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                If Dr("Type_Client") = "Entreprise" Then
                    str = Dr("Nom_entreprise")
                Else
                    str = Dr("Nom") & "  " & Dr("Prenom")
                End If
                htClient.Add(Dr.GetInt32(0), str)
            Loop
            Dr.Close()


            For Each ImageType As DictionaryEntry In htClient
                cmbClient.Items.Add(ImageType)
            Next
            cmbClient.DisplayMember = "value"
            cmbClient.ValueMember = "key"
            If cmbClient.Items.Count > 0 Then
                cmbClient.SelectedIndex = 0
            End If

            'remplir combo personelles
            htClient.Clear()
            Cmd.CommandText = "select * from Personnels where [categorie] like 'employé'"
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                htClient.Add(Dr.GetInt32(0), Dr.GetString(1) & " " & Dr.GetString(2))
            Loop
            Dr.Close()

            For Each ImageType As DictionaryEntry In htClient
                cmbPerson.Items.Add(ImageType)
            Next
            cmbPerson.DisplayMember = "value"
            cmbPerson.ValueMember = "key"
            If cmbPerson.Items.Count > 0 Then
                cmbPerson.SelectedIndex = 0
            End If

            'remplir Equipement
            remplirEquip()
            For Each equi In lstEquipement
                cmbCategorie.Items.Add(equi.categorie)
            Next

            Cmd.CommandText = "select ISNULL(CONVERT(int,MAX(numLocation)),0) + 1 from trLocation"
            txtNumBon.Text = Cmd.ExecuteScalar
            Me.dtpLivraison.MinDate = New Date(dtpLoc.Value.Year, dtpLoc.Value.Month, dtpLoc.Value.Day)
            Me.dtpRecup.MinDate = New Date(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day, dtpLivraison.Value.Hour, 0, 0).AddHours(5)
            'pour la modification
            If numLocation > 0 Then
                cmbClient.Enabled = False
                txtNumBon.Enabled = False
                txtNumBon.Text = numLocation
                dtpLoc.Enabled = False
                btn_ajouter.Text = "Modifier"
                btnRechClt.Enabled = False
                btnSuppLoc.Enabled = True
                Cmd.CommandText = "select tl.*,Nom_entreprise + c.Nom + ' ' + c.Prenom as fullName,p.Nom + ' ' + p.Prenom as livreur,tlt.idType,tte.idEquipement,tlt.quantite,tlt.prixUnit,tte.libelle from Clients c " &
                                    "inner join locEvent le on le.id = c.Id_Client " &
                                    "inner join trLocation tl on tl.idLocEvent = le.id " &
                                    "inner join Personnels p on p.Id_Personnels = tl.chargeLivraison " &
                                    "inner join trLocTypeEquip tlt on tlt.numLoc = tl.numLocation " &
                                    "inner join trTypeEquipement tte on tte.idType = tlt.idType " &
                                    "where tl.numLocation = " & numLocation
                Dr = Cmd.ExecuteReader
                Dim i As Integer = 0
                While Dr.Read()
                    If i = 0 Then
                    Dim mystr As String = Dr.GetString(7)
                    If mystr(mystr.Length - 1) = " " Then
                        mystr = mystr.Remove(mystr.Length - 1)
                    End If
                    cmbClient.SelectedItem = mystr
                    txtNumBon.Text = Dr.GetInt32(0)
                    dtpLoc.Value = Dr.GetDateTime(3).ToShortDateString()
                        dtpLivraison.Value = Dr.GetDateTime(4)
                        dtpRecup.Value = Dr.GetDateTime(5)
                    cmbPerson.SelectedItem = Dr.GetString(8)
                    txtAdresse.Text = Dr.GetString(6)

                    End If
                    For Each equi As equipement In lstEquipement
                        If equi.idEquip = Dr.GetInt32(10) Then
                            objEquipement = equi
                            Exit For
                        End If
                    Next

                    objTypEquip = New typeEquipement
                    With objTypEquip
                        .idType = Dr.GetInt32(9)
                        .quantStock = Dr.GetInt32(11)
                        .prixLoc = Dr.GetDouble(12)
                        .libelle = Dr.GetString(13)
                    End With
                    lstVEquipLoue.Items.Add(New ListViewItem(New String() {objTypEquip.idType, objEquipement.categorie, objTypEquip.libelle, objTypEquip.quantStock, objTypEquip.prixLoc}))
                    i += 1
                End While
                Dr.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        End Try
    End Sub
    Private Sub txtQuantite_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtQuantite.KeyPress
        Dim allowedChars As String = "1234567890"
        If e.KeyChar = ChrW(8) Then
            e.Handled = False
        ElseIf allowedChars.IndexOf(e.KeyChar) = -1 Then
            e.Handled = True
        End If
    End Sub
    Private Sub txt_prix_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_prix.KeyPress
        Dim allowedChars As String = "1234567890,"
        If txt_prix.Text.Contains(",") Then
            allowedChars = "1234567890"
        End If
        If e.KeyChar = ChrW(8) Then
            e.Handled = False
        ElseIf allowedChars.IndexOf(e.KeyChar) = -1 Then
            e.Handled = True
        End If
    End Sub
    Private Sub btnRechClt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRechClt.Click
        Me.Enabled = False
        Frm_Recherche_clients.Show()
        rech = "tarikLoc"
    End Sub

    Private Sub cmbCategorie_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCategorie.SelectedIndexChanged
        If cmbCategorie.SelectedIndex >= 0 Then
            objEquipement = lstEquipement.Where(Function(y As equipement) y.categorie = cmbCategorie.Text)(0)
            open()
            objEquipement.mesTypes()
            cmbType.Items.Clear()
            For Each typeEqui In objEquipement.types
                cmbType.Items.Add(typeEqui.libelle)
            Next
        Else
            objEquipement = Nothing
            objTypEquip = Nothing
            lblValable.Text = ""
            txtQuantite.Clear()
            txt_prix.Clear()
            cmbType.Items.Clear()
        End If
        txtQuantite.Clear()
    End Sub

    Private Sub Radio_nuit_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        cmbType_SelectedIndexChanged(sender, e)
    End Sub
    Dim quantiteUtilise As Integer

    Structure quantiteVar
        Public idtype As Integer
        Public quantite As Integer
    End Structure
    Dim lstobjStrucQuant As New List(Of quantiteVar)
    Dim objStrucQuant As New quantiteVar
    Dim quantiteInclus As Integer
    Private Sub cmbType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbType.SelectedIndexChanged
        If cmbType.SelectedIndex >= 0 Then
            objTypEquip = objEquipement.types.Where(Function(y As typeEquipement) y.libelle = cmbType.Text)(0)
            txt_prix.Text = objTypEquip.prixLoc
            dteLoc = New System.DateTime(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day)
            Dim str As String = ""

            Cmd = New SqlCommand("select SUM(quantite) as quantiteLoue from trLocation tl inner join trLocTypeEquip tlte " &
                                "on tl.numLocation = tlte.numLoc " &
                                "where (dateLivraison <= @dteLiv " &
                                "and dateRecuperation > @dteLiv) or (dateLivraison >= @dteLiv and dateLivraison < @dteRecup) " &
                                "group by idType having idType = " & objTypEquip.idType, Con)

            dteLoc = New System.DateTime(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day, dtpLivraison.Value.Hour, dtpLivraison.Value.Minute, 0)
            Cmd.Parameters.AddWithValue("@dteLiv", dteLoc)
            dteLoc = New System.DateTime(dtpRecup.Value.Year, dtpRecup.Value.Month, dtpRecup.Value.Day, dtpRecup.Value.Hour, dtpRecup.Value.Minute, 0)
            Cmd.Parameters.AddWithValue("@dteRecup", dteLoc)
            quantiteUtilise = convertDbnullToInt(Cmd.ExecuteScalar)
            'For Each itEquip As ListViewItem In lstVEquipLoue.Items
            '    If CInt(itEquip.SubItems(0).Text) = objTypEquip.idType Then
            '        quantiteUtilise += CInt(itEquip.SubItems(3).Text)
            '        Exit For
            '    End If
            'Next
            lblValable.Text = objTypEquip.quantStock - quantiteUtilise - 10
        Else
            objTypEquip = Nothing
            lblValable.Text = ""
        End If
    End Sub

    Private Sub btnAddAdj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddAdj.Click
        If cmbCategorie.SelectedIndex >= 0 And cmbType.SelectedIndex >= 0 And Not String.IsNullOrEmpty(txt_prix.Text) _
            And Not String.IsNullOrWhiteSpace(txt_prix.Text) And Not String.IsNullOrEmpty(txtQuantite.Text) And Not String.IsNullOrWhiteSpace(txtQuantite.Text) Then

            Dim it As IEnumerable(Of ListViewItem) = From i As ListViewItem In lstVEquipLoue.Items Where i.SubItems(0).Text = objTypEquip.idType
            If CInt(txtQuantite.Text) <= CInt(lblValable.Text) And CInt(lblValable.Text) <> 0 Then
                If it.Count <= 0 Then
                    lstVEquipLoue.Items.Add(New ListViewItem(New String() {objTypEquip.idType, objEquipement.categorie, objTypEquip.libelle, txtQuantite.Text, txt_prix.Text}))
                Else
                    MsgBox("l'équipement existe déjà")
                    Exit Sub
                End If
            Else
                MsgBox("Stock insuffisant")
                Exit Sub
            End If

            If numLocation <> 0 Then
                Cmd.Parameters.Clear()
                Cmd.Parameters.Add("prixLoc", SqlDbType.Float)
                Cmd.CommandText = "insert into trLocTypeEquip values(" & objTypEquip.idType & "," & CInt(txtNumBon.Text) & ",@prixLoc," & txtQuantite.Text & ")"
                Cmd.Parameters(0).Value = CDbl(txt_prix.Text)
                Cmd.ExecuteNonQuery()
                Cmd.Parameters.Clear()
            End If

            'initialiser
            cmbCategorie.SelectedIndex = -1
            cmbType.SelectedIndex = -1
            txtQuantite.Clear()
            txt_prix.Clear()
        Else
            MsgBox("Vous devez remplir tous les champs de l'équipement loué")
        End If


    End Sub

    Private Sub btnDeleteAdj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteAdj.Click
        If lstVEquipLoue.SelectedItems.Count > 0 Then
            If numLocation > 0 Then
                Cmd.Parameters.Clear()
                Cmd.CommandText = "delete from trLocTypeEquip where idType= " & lstVEquipLoue.SelectedItems(0).SubItems(0).Text
                Cmd.ExecuteNonQuery()
            End If
            lstVEquipLoue.SelectedItems(0).Remove()
            cmbCategorie.SelectedIndex = -1
        End If
    End Sub

    Private Sub btn_ajouter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter.Click
        If Not cmbClient.SelectedItem Is Nothing And Not cmbPerson.SelectedItem Is Nothing And Not String.IsNullOrWhiteSpace(txtNumBon.Text) And Not String.IsNullOrEmpty(txtNumBon.Text) _
             Then

            If numLocation = 0 Then
                If lstVEquipLoue.Items.Count > 0 Then
                    Try
                        'Remplir la table d'héritage(voir le diagrame)
                        Cmd.Parameters.Clear()
                        dteLoc = New System.DateTime(dtpLoc.Value.Year, dtpLoc.Value.Month, dtpLivraison.Value.Day)
                        Cmd.CommandText = "INSERT INTO [trLocation] values(" & txtNumBon.Text & "," & CType(cmbClient.SelectedItem, DictionaryEntry).Key & "," & CType(cmbPerson.SelectedItem, DictionaryEntry).Key & _
                       ",@dteLoc,@dteLiv,@dteRecup,@adresse)"
                        Cmd.Parameters.AddWithValue("@dteLoc", dteLoc)
                        Cmd.Parameters.AddWithValue("@adresse", txtAdresse.Text)
                        dteLoc = Nothing
                        dteLoc = New System.DateTime(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day, dtpLivraison.Value.Hour, dtpLivraison.Value.Minute, 0)
                        Cmd.Parameters.AddWithValue("@dteLiv", dteLoc)
                        dteLoc = New System.DateTime(dtpRecup.Value.Year, dtpRecup.Value.Month, dtpRecup.Value.Day, dtpRecup.Value.Hour, dtpRecup.Value.Minute, 0)
                        Cmd.Parameters.AddWithValue("@dteRecup", dteLoc)
                        Cmd.ExecuteNonQuery()
                    Catch ex As SqlException
                        If ex.Message.Contains("PK_location") Then
                            MsgBox("Le numéro de la demande exisite déjà")
                        Else
                            MsgBox(ex.Message)
                        End If
                        Exit Sub
                    End Try

                    Cmd.Parameters.Clear()
                    Cmd.Parameters.Add("prixLoc", SqlDbType.Float)
                    For Each it As ListViewItem In lstVEquipLoue.Items
                        Cmd.CommandText = "insert into trLocTypeEquip values(" & it.SubItems(0).Text & "," & CInt(txtNumBon.Text) & ",@prixLoc," & it.SubItems(3).Text & ")"
                        Cmd.Parameters(0).Value = CDbl(it.SubItems(4).Text)
                        Cmd.ExecuteNonQuery()
                    Next
                    vider()
                    MsgBox("Ajout de demande réussi", MsgBoxStyle.Information, "Succés")
                Else
                    MsgBox("Vous n'avez pas ajouté les équipements")
                End If
            Else
                Cmd.Parameters.Clear()
                dteLoc = New System.DateTime(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day, dtpLivraison.Value.Hour, dtpLivraison.Value.Minute, 0)
                Cmd.CommandText = "update trLocation set dateLivraison = @dteLiv,dateRecuperation=@dteRecup,adresse = @adresse where numLocation = " & numLocation
                Cmd.Parameters.AddWithValue("@dteLiv", dteLoc)
                dteLoc = New System.DateTime(dtpRecup.Value.Year, dtpRecup.Value.Month, dtpRecup.Value.Day, dtpRecup.Value.Hour, dtpRecup.Value.Minute, 0)
                Cmd.Parameters.AddWithValue("@dteRecup", dteLoc)
                Cmd.Parameters.AddWithValue("@adresse", txtAdresse.Text)
                Cmd.ExecuteNonQuery()
                cmbCategorie.SelectedIndex = -1
                MsgBox("Modification de la demande réussi", MsgBoxStyle.Information, "Succés")
            End If

        Else
            MsgBox("Rassurez vous que tous les champs sont remplis")
        End If
    End Sub

    Private Sub dtpLivraison_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpLivraison.ValueChanged, dtpRecup.ValueChanged
        Me.dtpRecup.MinDate = New Date(dtpLivraison.Value.Year, dtpLivraison.Value.Month, dtpLivraison.Value.Day, dtpLivraison.Value.Hour, 0, 0).AddHours(5)
        cmbType_SelectedIndexChanged(sender, e)
    End Sub


    Private Sub dtpLoc_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpLoc.ValueChanged
        Me.dtpLivraison.MinDate = New Date(dtpLoc.Value.Year, dtpLoc.Value.Month, dtpLoc.Value.Day)
    End Sub

    Private Sub btn_nouv_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nouv.Click
        vider()
    End Sub

    Private Sub btnLstLoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLstLoc.Click
        frmLstLocation.MdiParent = Frm_Main
        Me.Close()
        frmLstLocation.Show()
    End Sub

    Private Sub Frm_Demande_Clients_loc_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed
        numLocation = 0
        cnClose()
    End Sub

    Private Sub btnSuppLoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSuppLoc.Click
        If MsgBox("Voulez vous vraiment supprimer cette location?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Suppression") = MsgBoxResult.Yes Then
            Cmd.Parameters.Clear()
            Cmd.CommandText = "delete from trLocation where numLocation = " & numLocation
            Cmd.ExecuteNonQuery()
            vider()
            MsgBox("Suppression de la location réussi", MsgBoxStyle.Information, "Succés")
            numLocation = 0
            cmbClient.Enabled = True
            txtNumBon.Enabled = True
            dtpLoc.Enabled = True
            btn_ajouter.Text = "Ajouter"
            btnRechClt.Enabled = True
            btnSuppLoc.Enabled = False

            Cmd.CommandText = "select ISNULL(CONVERT(int,MAX(numLocation)),0) + 1 from trLocation"
            txtNumBon.Text = Cmd.ExecuteScalar
        End If
    End Sub
End Class